package com.zr.sms.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zr.sms.dao.ShopListDao;
import com.zr.sms.model.Shoplist;
import com.zr.sms.util.JDBCUtil;

public class ShopListDaoImpl implements ShopListDao{

	
	public boolean insertShopList(String sdate,String scontent,double stotalmoney, int eid) {
		Connection con=null;
		PreparedStatement stmt=null;
		boolean flag=false;
		
		con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("insert into shoplist (sdate,scontent,stotalmoney,eid) values(?,?,?,?)");
		try {
			stmt=con.prepareStatement(sql.toString());
			stmt.setString(1, sdate);
		    stmt.setString(2, scontent);
		    stmt.setDouble(3, stotalmoney);
		    stmt.setInt(4, eid);
		    int a=stmt.executeUpdate();
		    if(a>0){
		    	flag=true;
		    }
		    
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}


	public Shoplist selectShopListBySid(int sid) {
		Connection con=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		Shoplist s=new Shoplist();
		
		con=JDBCUtil.getConnection();
		StringBuffer sql=new StringBuffer("select * from shoplist where sid=?");
		
		try {
			stmt=con.prepareStatement(sql.toString());
			stmt.setInt(1, sid);
			rs=stmt.executeQuery();
			if(rs.next()){
				s.setSid(rs.getInt("sid"));
				s.setSdate(rs.getString("sdate"));
				s.setScontent(rs.getString("scontent"));
				s.setStotalmoney(rs.getDouble("stotalmoney"));
				s.setEid(rs.getInt("eid"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return s;
	}

	
	public boolean updateScontentBySid(String scontent,double stotalmoney,int sid) {
	   Connection con=null;
	   PreparedStatement stmt=null;
	   boolean flag=false;
	   con=JDBCUtil.getConnection();
	   StringBuffer sql=new StringBuffer("update  shoplist set scontent=?,stotalmoney=? where sid=?");
	   try {
		stmt=con.prepareStatement(sql.toString());
		stmt.setString(1, scontent);
		stmt.setDouble(2, stotalmoney);
		stmt.setInt(3, sid);
		int a=stmt.executeUpdate();
		if(a>0){
			flag=true;
			
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	   
		return flag;
	}
	
	@Override
	public List<Shoplist> getShopLists(int start, int pageSize) {
		List<Shoplist> ls = new ArrayList<Shoplist>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select sid,sdate,scontent,stotalmoney,eid from  shoplist");
		sql.append(" limit ?,?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Shoplist s = new Shoplist();
				s.setSid(rs.getInt("sid"));
				s.setSdate(rs.getString("sdate"));
				s.setScontent(rs.getString("scontent"));
				s.setStotalmoney(rs.getDouble("stotalmoney"));
				s.setEid(rs.getInt("eid"));
				ls.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ls;
	}

	@Override
	public int getSlsConut() {
		int scount = 0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(sid) scount from shoplist");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scount;
	}

	@Override
	public int getSlConut(String searchdate) {
		int  scount =  0;
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select count(sid) scount from  shoplist where sdate like ?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchdate+"%");
			ResultSet  rs = pst.executeQuery();
			if(rs.next()) {
				scount = rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return scount;
	}

	@Override
	public List<Shoplist> getShopLists(int start, int pageSize, String searchdate) {
		List<Shoplist>  sls = new ArrayList<Shoplist>();
		Connection   con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("select sid,sdate,scontent,stotalmoney,eid from  shoplist");
		sql.append(" where sdate like ? ");
		sql.append(" limit ?,?");
		try {
			PreparedStatement   pst =  con.prepareStatement(sql.toString());
			pst.setString(1, "%"+searchdate+"%");
			pst.setInt(2, start);
			pst.setInt(3, pageSize);
			ResultSet  rs = pst.executeQuery();
			while(rs.next()) {
				Shoplist s = new Shoplist();
				s.setSid(rs.getInt("sid"));
				s.setSdate(rs.getString("sdate"));
				s.setScontent(rs.getString("scontent"));
				s.setStotalmoney(rs.getDouble("stotalmoney"));
				s.setEid(rs.getInt("eid"));
				sls.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sls;
}
}



